package com.gugee.ins.common.dal.mapper.dic;

import com.gugee.common.mapper.GugeeMapper;
import com.gugee.ins.common.model.mysql.dic.InsDicLocation;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;


/**
 * <p>
 * ins博主地理位置字典表 Mapper 接口
 * </p>
 *
 * @author huang xin
 * @since 2020-12-30
 */
public interface InsDicLocationMapper extends GugeeMapper<InsDicLocation> {

    /**
     * 根据 截取cityName获得的region搜索国家名称
     * @param region
     * @return
     */
    @Select("select t.dic_nation_code from ins_dic_location t where t.nation =  #{region} or t.province =  #{region} " +
            " or t.city =  #{region} or t.county =  #{region} or t.local_city_name = #{region} " +
            " group by dic_nation_code")
    List<String> findNationByRegion(@Param("region")String region);

    /**
     * 根据 uid 查找location关系表 映射国家名称
     * @param uid
     * @return
     */
    @Select("SELECT DISTINCT t2.dic_nation_code FROM ( SELECT DISTINCT   " +
            " case when INSTR(location_name,',') > 0 then " +
            " TRIM(REVERSE(left(REVERSE(location_name),LOCATE(',',REVERSE(location_name))-1))) else  " +
            " TRIM(location_name) end location_name FROM ins_rel_blogger_location WHERE uid = #{uid}) t1 " +
            " INNER JOIN ins_dic_location t2 ON t1.location_name = t2.nation OR t1.location_name = t2.province " +
            " or t1.location_name = t2.city or t1.location_name = t2.county or t1.location_name = t2.local_city_name ")
    List<String> findNationByUid(@Param("uid")long uid);
}
